/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yunhoo.govProcure.bean;

import com.yunhoo.base.model.Department;
import com.yunhoo.base.model.Person;
import com.yunhoo.govProcure.model.CateFormModel;
import com.yunhoo.govProcure.model.CateStatsBase;
import com.yunhoo.sys.db.util.IbatisTemplate;
import com.yunhoo.sys.util.common.CommonParam;
import com.yunhoo.sys.util.common.LoginParam;
import com.yunhoo.sys.util.common.Null;
import com.yunhoo.sys.util.common.ToolKit;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.DataModel;
import javax.faces.model.ListDataModel;
import javax.faces.model.SelectItem;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CreationHelper;

@ManagedBean(name = "cateBudgetTotalGovBean")
@SessionScoped
public class CateBudgetTotalGovBean {

    private final static String NAME_SPACE = "budgetTotalGov.";
    //
    private List<Integer> yearli = null;
    //    
    private CateStatsBase curRow = null;
    private Integer selectedRowId = -1;
    private Integer iyear = 0;
    //
    private List<CateStatsBase> totalli = null;
    private List<CateStatsBase> detailist = null;
    private List<CateStatsBase> statslist = new ArrayList<CateStatsBase>();
    private DataModel<CateStatsBase> statslistModel = new ListDataModel<CateStatsBase>();
    private List<CateStatsBase> statsli = new ArrayList<CateStatsBase>();
    private DataModel<CateStatsBase> statsliModel = new ListDataModel<CateStatsBase>();
    private List<CateStatsBase> statsliFor = new ArrayList<CateStatsBase>();
    private DataModel<CateStatsBase> statsliForModel = new ListDataModel<CateStatsBase>();
    private List<CateStatsBase> statsliFive = new ArrayList<CateStatsBase>();
    private DataModel<CateStatsBase> statsliFiveModel = new ListDataModel<CateStatsBase>();
    private List<CateStatsBase> statsliSix = new ArrayList<CateStatsBase>();
    private DataModel<CateStatsBase> statsliSixModel = new ListDataModel<CateStatsBase>();
    private List<CateFormModel> statsliSeven = new ArrayList<CateFormModel>();
    private DataModel<CateStatsBase> statsliSevenModel = new ListDataModel<CateStatsBase>();
    private List<CateFormModel> statsliEight = new ArrayList<CateFormModel>();
    private DataModel<CateStatsBase> statsliEightModel = new ListDataModel<CateStatsBase>();
    // 
    private String status = "";

    public CateBudgetTotalGovBean() {
    }

    public void querylist() {
        if (!Null.check(totalli)) {
            totalli.clear();
        }
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("iyear", iyear);
        map.put("status", status);
        if (iyear != null && iyear > 0) {
            //totalli = dao.queryList(map, NAME_SPACE);
            totalli = IbatisTemplate.queryForList(NAME_SPACE + "queryList", map);
            System.out.println("totalli: " + totalli.size());
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    private String getOrgCode() {
        return LoginParam.getOrg().getCode();
    }

    public void rowSelectedli() {
        System.out.println("selectedRowId1:  " + selectedRowId);
        curRow = totalli.get(selectedRowId);
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("cateCode", curRow.getCateCode());
        map.put("quality", curRow.getQuality());
        map.put("iyear", iyear);
        map.put("status", status);
        //detailist = dao.queryList03(map, NAME_SPACE);
        detailist = IbatisTemplate.queryForList(NAME_SPACE + "queryList03", map);
    }

    public void queryli() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("status", status);
        map.put("iyear", iyear);
        if (iyear != null && iyear > 0) {
            //statslist = dao.queryList01(map, NAME_SPACE);
            statslist = IbatisTemplate.queryForList(NAME_SPACE + "queryList01", map); 
            statslistModel.setWrappedData(statslist);
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    public void query() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("status", status);
        map.put("iyear", iyear);
        if (iyear != null && iyear > 0) {
            //statsli = dao.queryList02(map, NAME_SPACE);
            statsli = IbatisTemplate.queryForList(NAME_SPACE + "queryList02", map);
            statsliModel.setWrappedData(statsli);
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    public void queryliFor() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("status", status);
        map.put("iyear", iyear);
        if (iyear != null && iyear > 0) {
            //statsliFor = dao.queryList04(map, NAME_SPACE);
            statsliFor = IbatisTemplate.queryForList(NAME_SPACE + "queryList04", map);
            statsliForModel.setWrappedData(statsliFor);
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    public void queryliFive() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("iyear", iyear);
        map.put("status", status);
        map.put("deptCode", getCurDept().getCode().trim());
        if (iyear != null && iyear > 0) {
            //statsliFive = dao.queryList05(map, NAME_SPACE);
            statsliFive = IbatisTemplate.queryForList(NAME_SPACE + "queryList05", map);
            statsliFiveModel.setWrappedData(statsliFive);
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    public void queryliSix() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("iyear", iyear);
        map.put("status", status);
        map.put("deptCode", getCurDept().getCode().trim());
        map.put("perNumber", getCurPer().getCpsn_number().trim());
        if (iyear != null && iyear > 0) {
            //statsliSix = dao.queryList05(map, NAME_SPACE);
            statsliSix = IbatisTemplate.queryForList(NAME_SPACE + "queryList05", map);
            statsliSixModel.setWrappedData(statsliSix);
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    public void queryliSeven() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("iyear", iyear);
        map.put("status", status);
        if (iyear != null && iyear > 0) {
            //statsliSeven = dao.queryList06(map, NAME_SPACE);
            statsliSeven = IbatisTemplate.queryForList(NAME_SPACE + "queryList06", map);
            statsliSevenModel.setWrappedData(statsliSeven);
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    public void queryliEight() {
        Map map = new HashMap();
        map.put("orgCode", getOrgCode());
        map.put("iyear", iyear);
        map.put("status", status);
        if (iyear != null && iyear > 0) {
            //statsliEight = dao.queryList07(map, NAME_SPACE);
            statsliEight = IbatisTemplate.queryForList(NAME_SPACE + "queryList07", map);
            statsliEightModel.setWrappedData(statsliEight);
        } else {
            ToolKit.msgs_err("请选择年度!");
        }
    }

    public List<Integer> getYearli() {
        if (yearli == null) {
            // yearli = dao.queryList(null, "iyear");
            yearli = CommonParam.yearListOfInteger();
        }

        return yearli;
    }

    public void exportExcelFor() {
        if (totalli != null && totalli.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("表一(支出类别)");
            String[] excelColumns = initTabeleHeaderFor();
            List<CateStatsBase> rows = totalli;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("表一(支出类别)");
            HSSFRow headerRow = demoSheet.createRow(0);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 1;
            for (CateStatsBase row : rows) {
                createTableRowFor(demoWorkBook, demoSheet, row, rowIndex);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeaderFor() {
        String[] excelColumns = {"支出类别", "品目", "数量", "金额"};
        return excelColumns;
    }

    public static void createTableRowFor(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateStatsBase cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        if (Null.check(cate.getQuality())) {
            cell.setCellValue("合计");
        } else if (Null.check(cate.getCateName())) {
            cell.setCellValue(functionStatus().get(cate.getQuality()));
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(cate.getCateName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(cate.getCountTotal()));
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(cate.getBudget()));
        cell.setCellStyle(dstyle);

    }

    private static Map<String, String> functionStatus() {
        Map<String, String> numsta = new HashMap<String, String>();
        numsta.put("0", "基本支出");
        numsta.put("1", "项目支出");
        return numsta;
    }

    public void exportExcel() {
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFSheet demoSheet = demoWorkBook.createSheet("表二(支出类别-部门)");
        String[] excelColumns = initTabeleHeader();
        List<CateStatsBase> rows = statslist;

        HSSFHeader header = demoSheet.getHeader();
        header.setCenter("表二(支出类别-部门)");
        HSSFRow headerRow = demoSheet.createRow(0);
        for (int i = 0; i < excelColumns.length; i++) {
            HSSFCell headerCell = headerRow.createCell((short) i);
            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            headerCell.setCellValue(excelColumns[i]);
        }
        //填充数据
        int rowIndex = 1;
        for (CateStatsBase row : rows) {
            createTableRow(demoWorkBook, demoSheet, row, rowIndex);
            rowIndex++;
        }
        exportExcel(demoWorkBook, demoSheet);

    }
    //初始化表头信息

    public String[] initTabeleHeader() {
        String[] excelColumns = {"支出类别", "部门", "品目", "数量", "金额"};
        return excelColumns;
    }

    public static void createTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateStatsBase cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        if (Null.check(cate.getQuality())) {
            cell.setCellValue("合计");
        } else if (Null.check(cate.getDeptName())) {
            cell.setCellValue(functionStatus().get(cate.getQuality()));
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(1);
        if (cate.getCateName().equals("")) {
            cell.setCellValue(Null.convert(cate.getDeptName()));
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(cate.getCateName()));
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(cate.getCountTotal()));
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(cate.getBudget()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelF() {
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFSheet demoSheet = demoWorkBook.createSheet("表三(部门-支出类别-项目)");
        String[] excelColumns = initTabeleHeaderF();
        List<CateStatsBase> rows = statsli;

        HSSFHeader header = demoSheet.getHeader();
        header.setCenter("表三(部门-支出类别-项目)");
        HSSFRow headerRow = demoSheet.createRow(0);
        for (int i = 0; i < excelColumns.length; i++) {
            HSSFCell headerCell = headerRow.createCell((short) i);
            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            headerCell.setCellValue(excelColumns[i]);
        }
        //填充数据
        int rowIndex = 1;
        for (CateStatsBase row : rows) {
            createTableRowF(demoWorkBook, demoSheet, row, rowIndex);
            rowIndex++;
        }
        exportExcel(demoWorkBook, demoSheet);

    }

    //初始化表头信息
    public String[] initTabeleHeaderF() {
        String[] excelColumns = {"部门", "支出类别", "项目", "品目", "数量", "单价", "金额"};
        return excelColumns;
    }

    public static void createTableRowF(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateStatsBase cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        dstyled.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        if (Null.check(cate.getDeptName())) {
            cell.setCellValue("合计");
        } else if (Null.check(cate.getQuality())) {
            cell.setCellValue(Null.convert(cate.getDeptName()));
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(1);
        if (Null.check(cate.getProjName()) && Null.check(cate.getCateName())) {
            cell.setCellValue(functionStatus().get(cate.getQuality()));
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(2);
        if (cate.getCateName().equals("")) {
            cell.setCellValue(Null.convert(cate.getProjName()));
            cell.setCellStyle(dstyled);
        } else if (Null.check(cate.getProjName()) && !Null.check(cate.getQuality())
                && cate.getQuality().equals("0") && !Null.check(cate.getCateName())) {
            cell.setCellValue("--");
            cell.setCellStyle(dstyled);
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(cate.getCateName()));
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(cate.getCountTotal()));
        cell = row.createCell(5);
        cell.setCellValue(Null.convert(cate.getBudget() / cate.getCountTotal()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(6);
        cell.setCellValue(Null.convert(cate.getBudget()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelFour() {
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFSheet demoSheet = demoWorkBook.createSheet("表四(项目-品目)");
        String[] excelColumns = initTabeleHeaderFour();
        List<CateStatsBase> rows = statsliFor;

        HSSFHeader header = demoSheet.getHeader();
        header.setCenter("财政项目预算汇总表");
        HSSFRow headerRow = demoSheet.createRow(0);
        for (int i = 0; i < excelColumns.length; i++) {
            HSSFCell headerCell = headerRow.createCell((short) i);
            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            headerCell.setCellValue(excelColumns[i]);
        }
        //填充数据
        int rowIndex = 1;
        for (CateStatsBase row : rows) {
            createTableRowFour(demoWorkBook, demoSheet, row, rowIndex);
            rowIndex++;
        }
        exportExcel(demoWorkBook, demoSheet);

    }
    //初始化表头信息

    public String[] initTabeleHeaderFour() {
        String[] excelColumns = {"项目", "品目", "数量", "单价", "金额"};
        return excelColumns;
    }

    public static void createTableRowFour(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateStatsBase cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        if (cate.getCateName().equals("")) {
            cell.setCellValue(Null.convert(cate.getProjName()));
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(cate.getCateName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(cate.getCountTotal()));
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(cate.getBudget() / cate.getCountTotal()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(cate.getBudget()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelFive() {
        if (statsliFive != null && statsliFive.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("表五(品目)");
            String[] excelColumns = initTabeleHeaderFive();
            List<CateStatsBase> rows = statsliFive;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("表五(品目)");
            HSSFRow headerRow = demoSheet.createRow(0);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 1;
            for (CateStatsBase row : rows) {
                createTableRowFive(demoWorkBook, demoSheet, row, rowIndex);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeaderFive() {
        String[] excelColumns = {"品目", "数量", "金额"};
        return excelColumns;
    }

    public static void createTableRowFive(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateStatsBase cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(Null.convert(cate.getCateName()));
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(cate.getCountTotal()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(cate.getBudget()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelSix() {
        if (statsliSix != null && statsliSix.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("表六(品目)");
            String[] excelColumns = initTabeleHeaderSix();
            List<CateStatsBase> rows = statsliSix;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("表六(品目)");
            HSSFRow headerRow = demoSheet.createRow(0);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 1;
            for (CateStatsBase row : rows) {
                createTableRowSix(demoWorkBook, demoSheet, row, rowIndex);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeaderSix() {
        String[] excelColumns = {"品目", "数量", "金额"};
        return excelColumns;
    }

    public static void createTableRowSix(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateStatsBase cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(Null.convert(cate.getCateName()));
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(cate.getCountTotal()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(cate.getBudget()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelSeven() {
        if (statsliSeven != null && statsliSeven.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("品目汇总表");
            String[] excelColumns = initTabeleHeaderSeven();
            List<CateFormModel> rows = statsliSeven;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("品目汇总表");
            HSSFRow headerRow = demoSheet.createRow(2);
            HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i + 1);
                headerCell.setCellValue(excelColumns[i]);
                headerCell.setCellStyle(cellStyle);
            }
            //填充数据
            int rowIndex = 3;
            for (CateFormModel row : rows) {
                createTableRowSeven(demoWorkBook, demoSheet, row, rowIndex);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息 

    public String[] initTabeleHeaderSeven() {
        String[] excelColumns = {"小计", "基本支出", "项目支出", "小计", "财政资金", "其他资金", "小计", "集中采购", "部门集中采购",
            "分散采购", "小计", "公开招标", "邀请招标", "竞争性谈判", "单一来源", "询价"};
        return excelColumns;
    }

    public static void createTableRowSeven(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateFormModel cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 16));
        cell1 = row1.createCell(0);
        cell1.setCellValue("品目汇总表");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell2 = null;
        HSSFRow row2 = demoSheet.createRow(1);
        demoSheet.addMergedRegion(new Region((short) 1, (short) 0, (short) 2, (short) 0));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 1, (short) 1, (short) 3));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 4, (short) 1, (short) 6));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 7, (short) 1, (short) 10));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 11, (short) 1, (short) 16));
        cell2 = row2.createCell(0);
        cell2.setCellValue("品目");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(1);
        cell2.setCellValue("资金性质");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(4);
        cell2.setCellValue("资金来源");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(7);
        cell2.setCellValue("组织形式");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(11);
        cell2.setCellValue("采购方式");
        cell2.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        if (Null.check(cate.getCateName())) {
            cell.setCellValue("合计");
        } else {
            cell.setCellValue(Null.convert(cate.getCateName()));
        }
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(cate.getTotal1() + cate.getTotal2()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(cate.getTotal1()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(cate.getTotal2()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(cate.getTotal11() + cate.getTotal12()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(5);
        cell.setCellValue(Null.convert(cate.getTotal11()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(6);
        cell.setCellValue(Null.convert(cate.getTotal12()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(7);
        cell.setCellValue(Null.convert(cate.getTotal3() + cate.getTotal4() + cate.getTotal5()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(8);
        cell.setCellValue(Null.convert(cate.getTotal3()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(9);
        cell.setCellValue(Null.convert(cate.getTotal4()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(10);
        cell.setCellValue(Null.convert(cate.getTotal5()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(11);
        cell.setCellValue(Null.convert(cate.getTotal6() + cate.getTotal7() + cate.getTotal8() + cate.getTotal9() + cate.getTotal10()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(12);
        cell.setCellValue(Null.convert(cate.getTotal6()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(13);
        cell.setCellValue(Null.convert(cate.getTotal7()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(14);
        cell.setCellValue(Null.convert(cate.getTotal8()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(15);
        cell.setCellValue(Null.convert(cate.getTotal9()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(16);
        cell.setCellValue(Null.convert(cate.getTotal10()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelEight() {
        if (statsliEight != null && statsliEight.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("部门-品目汇总表");
            String[] excelColumns = initTabeleHeaderEight();
            List<CateFormModel> rows = statsliEight;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("部门-品目汇总表");
            HSSFRow headerRow = demoSheet.createRow(2);
            HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i + 2);
                headerCell.setCellValue(excelColumns[i]);
                headerCell.setCellStyle(cellStyle);
            }
            //填充数据
            int rowIndex = 3;
            for (CateFormModel row : rows) {
                createTableRowEight(demoWorkBook, demoSheet, row, rowIndex);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeaderEight() {
        String[] excelColumns = {"小计", "基本支出", "项目支出", "小计", "财政资金", "其他资金", "小计", "集中采购", "部门集中采购",
            "分散采购", "小计", "公开招标", "邀请招标", "竞争性谈判", "单一来源", "询价"};
        return excelColumns;
    }

    public static void createTableRowEight(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, CateFormModel cate, int rowIndex) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 16));
        cell1 = row1.createCell(0);
        cell1.setCellValue("部门-品目汇总表");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell2 = null;
        HSSFRow row2 = demoSheet.createRow(1);
        demoSheet.addMergedRegion(new Region((short) 1, (short) 0, (short) 2, (short) 0));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 1, (short) 2, (short) 1));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 2, (short) 1, (short) 4));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 5, (short) 1, (short) 7));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 8, (short) 1, (short) 11));
        demoSheet.addMergedRegion(new Region((short) 1, (short) 12, (short) 1, (short) 17));
        cell2 = row2.createCell(0);
        cell2.setCellValue("部门");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(1);
        cell2.setCellValue("品目");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(2);
        cell2.setCellValue("资金性质");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(5);
        cell2.setCellValue("资金来源");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(8);
        cell2.setCellValue("组织形式");
        cell2.setCellStyle(cellStyle);
        cell2 = row2.createCell(12);
        cell2.setCellValue("采购方式");
        cell2.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        if (Null.check(cate.getDeptName())) {
            cell.setCellValue("合计");
        } else if (!Null.check(cate.getDeptName()) && Null.check(cate.getCateName())) {
            cell.setCellValue(Null.convert(cate.getDeptName()));
        }
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(cate.getCateName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(cate.getTotal1() + cate.getTotal2()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(cate.getTotal1()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(cate.getTotal2()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(5);
        cell.setCellValue(Null.convert(cate.getTotal11() + cate.getTotal12()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(6);
        cell.setCellValue(Null.convert(cate.getTotal11()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(7);
        cell.setCellValue(Null.convert(cate.getTotal12()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(8);
        cell.setCellValue(Null.convert(cate.getTotal3() + cate.getTotal4() + cate.getTotal5()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(9);
        cell.setCellValue(Null.convert(cate.getTotal3()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(10);
        cell.setCellValue(Null.convert(cate.getTotal4()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(11);
        cell.setCellValue(Null.convert(cate.getTotal5()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(12);
        cell.setCellValue(Null.convert(cate.getTotal6() + cate.getTotal7() + cate.getTotal8() + cate.getTotal9() + cate.getTotal10()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(13);
        cell.setCellValue(Null.convert(cate.getTotal6()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(14);
        cell.setCellValue(Null.convert(cate.getTotal7()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(15);
        cell.setCellValue(Null.convert(cate.getTotal8()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(16);
        cell.setCellValue(Null.convert(cate.getTotal9()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(17);
        cell.setCellValue(Null.convert(cate.getTotal10()));
        cell.setCellStyle(dstyle);

    }

    public static void exportExcel(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet) {
        try {
            String strfileName = "book.xls";
            FileOutputStream fos = null;
            //导出
            demoSheet.setGridsPrinted(true);//demoSheet
            HSSFFooter footer = demoSheet.getFooter();
            footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
            // 获得JSF上下文环境
            FacesContext context = FacesContext.getCurrentInstance();
            // 获得ServletContext对象
            ServletContext servletContext = (ServletContext) context.getExternalContext().getContext();
            // 取得文件的绝对路径
            String excelName = servletContext.getRealPath("/upload") + "/" + strfileName;//strfileName
            File exportFile = new File(excelName);
            fos = new FileOutputStream(exportFile);//fos
            demoWorkBook.write(fos);//demoWorkBook
            fos.close();

            FacesContext ctx = FacesContext.getCurrentInstance();
            ctx.responseComplete();
            HttpServletResponse httpServletResponse = (HttpServletResponse) ctx.getExternalContext().getResponse();
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + strfileName);//strfileName
            httpServletResponse.setContentLength((int) exportFile.length());//exportFile
            httpServletResponse.setContentType("application/x-download");
            //httpServletResponse.setContentType("application/vnd.ms-excel");
            byte[] b = new byte[1024];
            int i = 0;
            FileInputStream fis = new java.io.FileInputStream(exportFile);
            while ((i = fis.read(b)) > 0) {
                servletOutputStream.write(b, 0, i);
            }
            servletOutputStream.flush();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private Department getCurDept() {
        return LoginParam.getDept();
    }

    private Person getCurPer() {
        return LoginParam.getPer();
    }

    public List<SelectItem> getSelectItemList() {
        List<SelectItem> selectItemList = new ArrayList<SelectItem>();
        selectItemList.add(new SelectItem("0,1,2,3", "所有"));
        selectItemList.add(new SelectItem("0", "未提交"));
        selectItemList.add(new SelectItem("1", "待审核"));
        selectItemList.add(new SelectItem("2", "返回修改"));
        selectItemList.add(new SelectItem("3", "审核通过"));
        return selectItemList;
    }

    public CateStatsBase getCurRow() {
        return curRow;
    }

    public void setCurRow(CateStatsBase curRow) {
        this.curRow = curRow;
    }

    public Integer getSelectedRowId() {
        return selectedRowId;
    }

    public void setSelectedRowId(Integer selectedRowId) {
        this.selectedRowId = selectedRowId;
    }

    public Integer getIyear() {
        return iyear;
    }

    public void setIyear(Integer iyear) {
        this.iyear = iyear;
    }

    public List<CateStatsBase> getTotalli() {
        return totalli;
    }

    public void setTotalli(List<CateStatsBase> totalli) {
        this.totalli = totalli;
    }

    public List<CateStatsBase> getDetailist() {
        return detailist;
    }

    public void setDetailist(List<CateStatsBase> detailist) {
        this.detailist = detailist;
    }

    public List<CateStatsBase> getStatslist() {
        return statslist;
    }

    public void setStatslist(List<CateStatsBase> statslist) {
        this.statslist = statslist;
    }

    public DataModel<CateStatsBase> getStatslistModel() {
        return statslistModel;
    }

    public void setStatslistModel(DataModel<CateStatsBase> statslistModel) {
        this.statslistModel = statslistModel;
    }

    public List<CateStatsBase> getStatsli() {
        return statsli;
    }

    public void setStatsli(List<CateStatsBase> statsli) {
        this.statsli = statsli;
    }

    public DataModel<CateStatsBase> getStatsliModel() {
        return statsliModel;
    }

    public void setStatsliModel(DataModel<CateStatsBase> statsliModel) {
        this.statsliModel = statsliModel;
    }

    public List<CateStatsBase> getStatsliFor() {
        return statsliFor;
    }

    public void setStatsliFor(List<CateStatsBase> statsliFor) {
        this.statsliFor = statsliFor;
    }

    public DataModel<CateStatsBase> getStatsliForModel() {
        return statsliForModel;
    }

    public void setStatsliForModel(DataModel<CateStatsBase> statsliForModel) {
        this.statsliForModel = statsliForModel;
    }

    public List<CateStatsBase> getStatsliFive() {
        return statsliFive;
    }

    public void setStatsliFive(List<CateStatsBase> statsliFive) {
        this.statsliFive = statsliFive;
    }

    public DataModel<CateStatsBase> getStatsliFiveModel() {
        return statsliFiveModel;
    }

    public void setStatsliFiveModel(DataModel<CateStatsBase> statsliFiveModel) {
        this.statsliFiveModel = statsliFiveModel;
    }

    public List<CateStatsBase> getStatsliSix() {
        return statsliSix;
    }

    public void setStatsliSix(List<CateStatsBase> statsliSix) {
        this.statsliSix = statsliSix;
    }

    public DataModel<CateStatsBase> getStatsliSixModel() {
        return statsliSixModel;
    }

    public void setStatsliSixModel(DataModel<CateStatsBase> statsliSixModel) {
        this.statsliSixModel = statsliSixModel;
    }

    public List<CateFormModel> getStatsliSeven() {
        return statsliSeven;
    }

    public void setStatsliSeven(List<CateFormModel> statsliSeven) {
        this.statsliSeven = statsliSeven;
    }

    public DataModel<CateStatsBase> getStatsliSevenModel() {
        return statsliSevenModel;
    }

    public void setStatsliSevenModel(DataModel<CateStatsBase> statsliSevenModel) {
        this.statsliSevenModel = statsliSevenModel;
    }

    public List<CateFormModel> getStatsliEight() {
        return statsliEight;
    }

    public void setStatsliEight(List<CateFormModel> statsliEight) {
        this.statsliEight = statsliEight;
    }

    public DataModel<CateStatsBase> getStatsliEightModel() {
        return statsliEightModel;
    }

    public void setStatsliEightModel(DataModel<CateStatsBase> statsliEightModel) {
        this.statsliEightModel = statsliEightModel;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}
